package dr.request.dao;

import java.sql.*;
import java.util.*;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import dr.request.domain.Request;

public class RequestDAO {
	
	private static RequestDAO instance = new RequestDAO();
	
	public static RequestDAO getInstance(){
		return instance;
	}
	
	private RequestDAO(){}
	
	/*-----------------------------------------------------------------DB 연결-----------------------------------------------------------------*/
	private Connection getConnection() throws Exception{
		Context initCtx = new InitialContext();
		Context envCtx = (Context)initCtx.lookup("java:comp/env");
		DataSource ds = (DataSource)envCtx.lookup("jdbc/orcl");
		return ds.getConnection();
	}
	
	/*----------------------------------------------------------------문의 게시판 글쓰기 처리-----------------------------------------------------*/
	public void insertArticla(Request req)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		int num = req.getNum();
		int ref = req.getRef();
		int re_step = req.getRe_step();
		int re_level = req.getRe_level();
		int number = 0;
		String sql = "";
		try{
			conn = getConnection();			
			System.out.println("num                 : "+num);
			if(num != 0){ //답글일 경우만 
			/* 답글작성시 같은 ref범위에서 writeForm에서 넘어온 re_step값보다 큰 re_step이 있으면 1씩 증가*/	
				sql = "update REQUEST set re_step = re_step+1 where ref=? and re_step > ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, ref);
				pstmt.setInt(2, re_step);
				pstmt.executeUpdate();
				
				re_step = re_step+1;
				re_level = re_level+1;
				
			}else{ //일반글일 경우
				pstmt = conn.prepareStatement("select max(num) from REQUEST");
				//ref 번호 부여하기 위해 수행
				rs = pstmt.executeQuery();
				if(rs.next()){
					number = rs.getInt(1)+1;
				}else{ //최초 글 작성시
					number = 1;
				}
				ref = number;
				re_step = 0;
				re_level = 0;
			}
			
			sql = "insert into REQUEST(num, id, subject, content, reg_date, ref, re_step, re_level, ip) "+
						      "values (request_seq.nextval, ?, ?, ?,SYSDATE, ?, ?, ?, ?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, req.getId());
			pstmt.setString(2, req.getSubject());
			pstmt.setString(3, req.getContent());
			pstmt.setInt(4, ref);
			pstmt.setInt(5, re_step);
			pstmt.setInt(6, re_level);
			pstmt.setString(7, req.getIp());
			
			pstmt.executeUpdate();
			
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(pstmt != null) try{pstmt.close();}catch(SQLException e){}
			if(conn != null) try{conn.close();}catch(SQLException e){}
		}
	}
	
	/*----------------------------------------------------------------전체 글목록의 수-----------------------------------------------------------*/
	public int getArticleCount(String keyField, String keyWord)throws Exception{
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		int x = 0;
		
		try{
			conn = getConnection();
			if(keyWord == null || "".equals(keyWord.trim())){ // 목록
				
				sql = "select count(*) from REQUEST";
				pstmt = conn.prepareStatement(sql);
				
			}else{ // 검색했을때 목록 
				//keyField = 컬럼명 , keyWord = 데이터
				sql = "select count(*) from REQUEST where "+keyField+ " like ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, "%"+keyWord+"%");
			}
			
			rs= pstmt.executeQuery();
			
			if(rs.next()){
				x = rs.getInt(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(rs != null)try{rs.close();}catch(SQLException e){}
			if(pstmt != null)try{pstmt.close();}catch(SQLException e){}
			if(conn != null)try{conn.close();}catch(SQLException e){}
		}
		return x;
	}
	
	/*----------------------------------------------------------------게시판의 글목록 보기------------------------------------------------------*/
	public List getArticles(int start, int end, String keyField, String keyWord)throws Exception{
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List requestList = null;
		String sql = "";
		
		try{
			conn = getConnection();
			
			if(keyWord == null || "".equals(keyWord.trim())){/*------------ 목록---------*/
				sql = "select *"+
			 	      "from (select a.* , rownum rnum "+
		 	                "from (select  num, id, subject, content, to_char(reg_date, 'YYYY-MM-DD') reg_date,ref, re_step, re_level, ip  "+
		 	                      "from REQUEST "+
		                          "order by ref desc, re_step asc)a) " +
		              "where rnum >= ? and rnum <=?";
				
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, start);
				pstmt.setInt(2, end);
				
			}else{		    /*------------------------------------------검색 목록---------*/
				sql = "select *"+
		 	          "from (select a.* , rownum rnum "+
	 	                    "from (select  num, id, subject, content, to_char(reg_date, 'YYYY-MM-DD') reg_date,ref, re_step, re_level, ip  "+
	 	                          "from REQUEST "+
	 	                          "where "+keyField+" like ? "+
	                              "order by ref desc, re_step asc)a) " +
	                  "where rnum >= ? and rnum <=?";	
				
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, keyWord);
				pstmt.setInt(2, start);
				pstmt.setInt(3, end);				
			}
			
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				requestList = new ArrayList(end);
				do{
					Request req = new Request();
					req.setNum(rs.getInt("num"));
					req.setId(rs.getString("id"));
					req.setSubject(rs.getString("subject"));
					req.setReg_date(rs.getString("reg_Date"));
					req.setRef(rs.getInt("ref"));
					req.setRe_step(rs.getInt("re_Step"));
					req.setRe_level(rs.getInt("re_Level"));
					req.setContent(rs.getString("content"));
					
					requestList.add(req);
					
				}while(rs.next());
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(rs != null)try{rs.close();}catch(SQLException e){}
			if(pstmt != null)try{pstmt.close();}catch(SQLException e){}
			if(conn != null)try{conn.close();}catch(SQLException e){}
		}
		return requestList;
	}
	
	/*----------------------------------------------------------------게시판글 상세 보기------------------------------------------------------*/
	public Request getContent(int num) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet  rs = null;
		Request req= null;
		
		try{
			conn = getConnection();
			pstmt = conn.prepareStatement("select num, id, subject, content, to_char(reg_date, 'YYYY-MM-DD') reg_date, ref, re_step, re_level  from REQUEST where num = ?");
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				req = new Request();
				
				req.setNum(rs.getInt("num"));
				req.setId(rs.getString("id"));
				req.setSubject(rs.getString("subject"));
				req.setContent(rs.getString("content"));
				req.setReg_date(rs.getString("reg_date"));
				req.setRef(rs.getInt("ref"));
				req.setRe_step(rs.getInt("re_step"));
				req.setRe_level(rs.getInt("re_level"));
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(rs != null) try{rs.close();}catch(SQLException e){}
			if(pstmt != null) try{pstmt.close();}catch(SQLException e){}
			if(conn != null) try{conn.close();} catch(SQLException e){}
		}
		return req;
	}
	
	
	/*----------------------------------------------------------------글 삭제-----------------------------------------------------------*/
	
	public int delete(int num) throws Exception{
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int x=-1;

		try{
			conn = getConnection();
			pstmt = conn.prepareStatement("select num from REQUEST where num =?");
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				pstmt = conn.prepareStatement("delete from REQUEST where num =?");
				pstmt.setInt(1, num);
				pstmt.executeUpdate();	   
				
				x=1;
			}else{
				x=0;
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(rs != null)try{rs.close();}catch(SQLException e){}
			if(pstmt != null)try{pstmt.close();}catch(SQLException e){}
			if(conn != null)try{conn.close();}catch(SQLException e){}
		}
		return x;
	}
	
	
	/*----------------------------------------------------------------수정할 데이터 가져오기----------------------------------------------*/
	public Request updateGetArticle(int num) throws Exception{
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs =null;
		Request req = null;
		
		try{
			conn = getConnection();
			pstmt = conn.prepareStatement("select * from REQUEST where num = ?");
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				req = new Request();
				
				req.setNum(rs.getInt("num"));
				req.setId(rs.getString("id"));
				req.setSubject(rs.getString("subject"));
				req.setReg_date(rs.getString("reg_date"));
				req.setRef(rs.getInt("ref"));
				req.setRe_step(rs.getInt("re_step"));
				req.setRe_level(rs.getInt("re_level"));
				req.setContent(rs.getString("content"));
				req.setIp(rs.getString("ip"));     
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(rs != null)try{rs.close();}catch(SQLException e){}
			if(pstmt != null)try{pstmt.close();}catch(SQLException e){}
			if(conn != null)try{conn.close();}catch(SQLException e){}
		}
		
		return req;
	}
	
	/*----------------------------------------------------------------수정하기--------------------------------------------------------*/
	public int updateArticle(Request req)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "";
		int x = -1;
		
		try{
			conn = getConnection();
			pstmt = conn.prepareStatement("select num from REQUEST where num = ?");
			pstmt.setInt(1, req.getNum());
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				sql = "update REQUEST set subject = ? , content = ? where num = ?";
				pstmt = conn.prepareStatement(sql);
				
				pstmt.setString(1, req.getSubject());
				pstmt.setString(2, req.getContent());
				pstmt.setInt(3, req.getNum());
				pstmt.executeUpdate();
				
				x = 1;
			}else{
				x = 0;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(rs != null)try{rs.close();}catch(SQLException e){}
			if(pstmt != null)try{pstmt.close();}catch(SQLException e){}
			if(conn != null)try{conn.close();}catch(SQLException e){}
		}
		
		return x;
	}
}
